#!/usr/bin/perl
use strict;
use DBI;
use HTTP::Date;
use Config::IniFiles;
use Time::Local;
use XML::LibXML;

require "/var/www/cgi-bin/gnugraph/rcoos/graphAll.lib";
require "/var/www/cgi-bin/gnugraph/rcoos/timeZone.lib";

my $graph_config="/var/myscheduler/graph.xml";
my $xp_graph = XML::LibXML->new->parse_file($graph_config);

my $tmp="/var/www/html/images2/";

# get local time
my $timeinterval=tellmeinterval();
my $dst=tellmeedt();


#my $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", "", "",
            #        { RaiseError => 1, AutoCommit => 1 });
#if(!defined $dbh) {die "Cannot connect to database!\n";}
 my $dbh=getdbConn();
 if($dbh==0){ die "Cannot connect to database!\n";}

 #get time stamp
 my ($timein1day,$timein3day,$timein1week, $timein1month,$timein1year);
 my $sqlsta="select TO_CHAR(current_timestamp-interval '1 day','YYYY-MM-DD hh24:mi'),TO_CHAR(current_timestamp-interval '3 day','YYYY-MM-DD hh24:mi'),TO_CHAR(current_timestamp-interval '1 week','YYYY-MM-DD hh24:mi'), TO_CHAR(current_timestamp-interval '1 month','YYYY-MM-DD hh24:mi'),TO_CHAR(current_timestamp-interval '1 year','YYYY-MM-DD hh24:mi')";
 my $sth = $dbh->prepare($sqlsta);
 $sth->execute();
 
 while (my @row = $sth->fetchrow_array) {
     $timein1day=substr($row[0],0,18);
     $timein3day=substr($row[1],0,18);
     $timein1week=substr($row[2],0,18);
     $timein1month=substr($row[3],0,18);
     $timein1year=substr($row[4],0,18);
 }
 $sth->finish();
 
     my (@tarray,$timeperiod,$tperiod);
     if((substr($timein1day,11,2) eq "06")&&(substr($timein1day,14,2)>3)){
        @tarray=("1_year","1_month");
        $tperiod="1_year";
     }
     else{

        @tarray=("1_day","3_day","1_week");
        $tperiod="1_week";
     }

 #query sensor table to retrieve the mtype id, sensor id, variable name and unit name
  my (@sensor,@zarray,@namearray,@uomarray,@platform);
  $sqlsta="select sensor.row_id,sensor.fixed_z,obs_type.standard_name,uom_type.standard_name,platform.short_name from sensor 
         left join m_type on m_type.row_id=sensor.m_type_id
         left join m_scalar_type on m_scalar_type.row_id=m_type.m_scalar_type_id
         left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id
         left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id
         left join platform on platform.row_id=sensor.platform_id
         where obs_type.multiple=0 and platform.active=true order by sensor.row_id asc";

     $sth = $dbh->prepare($sqlsta);
     $sth->execute();
     my @t;
     while (my @row = $sth->fetchrow_array) {
            push(@sensor,$row[0]);
            
            my($temptitle,$formula)=uom_convert($row[2],$row[3]);
            push(@zarray,$formula);

            if((substr($row[2],0,3) ne "air")&&(substr($row[2],0,4) ne "wind")&&$row[1]){
                 $row[2]=$row[2]."($row[1]m)";
            }
            push(@namearray,$row[2]);

            push(@uomarray,$temptitle);
            push(@platform,$row[4]);
     }
     $sth->finish();

     # now get the dataset for the variable from this platform



     for(my $n=0;$n<@sensor;$n++){

         my @tx=split(/_/,$tperiod);
         my $timeperiod=join(/ /,@tx);

         #print "\nI am here\n";

         $sqlsta="select TO_CHAR(m_date-'$timeinterval hour'::interval,'YYYY-MM-DD hh24:mi'),m_value from multi_obs where age(m_date)<'$timeperiod' and sensor_id=$sensor[$n] and (qc_level!=1 or qc_level is null) and m_value is not null order by m_date asc";
         $sth = $dbh->prepare($sqlsta);
         $sth->execute();

         my(@mdate,@mvalue);
         while (my @row = $sth->fetchrow_array) {
            $row[1]=value_convert($zarray[$n],$row[1]);
            push(@mdate,$row[0]);
            push(@mvalue,$row[1]);
         }
         $sth->finish();

         my $count=@mdate;

         for(my $m=0;$m<@tarray;$m++){
             my $period=$tarray[$m];
             my @tx=split(/_/,$period);

             my $timeperiod=join(/ /,@tx);

             my $graph_data_file =$tmp.'gnuplot_'.int(rand(10000000));
        
             my $graphname=$tmp.$platform[$n]."_".$sensor[$n]."_$period.png";
             if(-e $graphname){system("rm $graphname");}

             my $count=@mvalue;
             my $timestamp;
             if($period eq "1_day"){
                $timestamp=$timein1day;
             }     
            if($period eq "3_day"){
               $timestamp=$timein3day;
            } 
            if($period eq "1_week"){
               $timestamp=$timein1week;
            } 
           if($period eq "1_month"){
              $timestamp=$timein1month;
           } 
           if($period eq "1_year"){
              $timestamp=$timein1year;
           } 

           if(@mdate[$count-1] gt $timestamp){
               open(TEMPFILE,">$graph_data_file");
  
               my $ticstring="";
               my @temp;

               for(my $a=0;$a<@mdate;$a++){
                    if($mdate[$a] gt $timestamp){
                       print TEMPFILE $mdate[$a]."\t".$mvalue[$a]."\n";
                       push(@temp,"$mdate[$a]");
                    }
                    else { ;}
              }
              close(TEMPFILE);
                       
              my $title="$platform[$n] $namearray[$n]";
              my $y_label=$uomarray[$n];
              my $qc=0;
 
              my $ticstring="";
              if(@temp>1){
                 
                 my $start="'$temp[0]'";
                 my $end="'$temp[@temp-1]'";
                 my $sqlsta="select timestamp $start,timestamp $start+(timestamp $end-timestamp $start)/8,timestamp $start+(timestamp $end-timestamp $start)/4,timestamp $start+3*(timestamp $end-timestamp $start)/8,timestamp $start+(timestamp $end-timestamp $start)/2,timestamp $start+5*(timestamp $end-timestamp $start)/8,timestamp $start+6*(timestamp $end-timestamp $start)/8,timestamp $start+7*(timestamp $end-timestamp $start)/8,timestamp $end";
                 my $sth = $dbh->prepare($sqlsta);
                 $sth->execute();
                 #print $sqlsta."\n"
                 @temp=();
                 while (my @row = $sth->fetchrow_array) {
                     for(my $b=0;$b<@row;$b++){
                       $row[$b]="'".substr($row[$b],0,16)."'";
                       if(grep $_ eq $row[$b],@temp){  ;  }
                       else {$ticstring=$ticstring.$row[$b].",";}
                    }
                 }
                 $ticstring=substr($ticstring,0,-1);
              }
  

              #print "\n****$ticstring\n";
              my_graph($graph_data_file, $title, $y_label, $graphname,$tperiod,$qc,$dst,$ticstring); 
            }
            else {
               system("cp /var/www/html/images2/nodata.png $graphname");     
            }
                
         } # end of time period for loop

      }

   $dbh->disconnect();




sub uom_convert {

 my ($obs_type,$uom) = @_;

 my $unit_conversion = '';

 ##CONFIG_START
 #conversions for the following which were labeled wrong on the way in to db
 if ($obs_type eq 'dissolved_oxygen') { $obs_type = 'oxygen_concentration'; }
 if ($uom eq 'millibar') { $uom = 'mb'; }
 if ($uom eq 'percent_saturation') { $uom = 'percent'; }
 ##CONFIG_END

  my $unit_conversion = $xp_graph->findvalue('//observation_list/observation[@standard_name="'.$obs_type.'.'.$uom.'"]/standard_uom_en');
  if ($unit_conversion eq '') { print "not found: $obs_type $uom \n"; return ($uom,$unit_conversion); }

  my $y_title="";
  my $conversion_formula="";
  $y_title = $xp_graph->findvalue('//unit_conversion_list/unit_conversion[@id="'.$uom.'_to_'.$unit_conversion.'"]/y_title');
  if($y_title eq ""){$y_title=$uom;}
  $conversion_formula = $xp_graph->findvalue('//unit_conversion_list/unit_conversion[@id="'.$uom.'_to_'.$unit_conversion.'"]/conversion_formula');

 #unit conversion using supplied equation(e.g. celcius to fahrenheit)
 #my $conversion_string = $conversion_formula;
  #  $conversion_string =~ s/var1/$measurement/g;
 #my $conversion_val = eval $conversion_string;

 return ($y_title,$conversion_formula);

}

sub value_convert {

 my ($conversion_string,$measurement) = @_;
    $conversion_string =~ s/var1/$measurement/g;
 my $conversion_val = eval $conversion_string;
 return $conversion_val; 
}
